Database Management Systems (DBMS) allow the collection, organization, searching and processing of information in a variety of manners. Typically, data in such DBMS are stored in tables comprising columns, which define fields of information for a record, and rows, containing specific records of those fields of the database. Many modern DBMS employ Structured Query Language (SQL) as at least one of the mechanisms by which the DBMS can be controlled, including defining tables and views.
A view is a result set of records in the database which results from a query. Typically, a DBMS can perform SQL commands, such as SELECT, UPDATE, INSERT or DELETE against a view without being aware of the definition of the view and/or its underlying tables.
One example of a DBMS is the IBM® DB2® Universal Database manufactured and sold by IBM and the SQL examples used herein employ DB2 SQL statements. As one of its advanced features, this DBMS permits a UNION ALL view to be created wherein two or more physically separate tables are logically merged into one view for certain types of processing. Amongst other advantages, this allows large databases which, if stored as a single table would exceed various DBMS and/or hardware limits, to instead be physically partitioned into a variety of smaller tables. For example, if several years global sales data for a corporation is stored in a single table, that table could easily exceed the maximum table size that a DBMS will allow. With a UNION ALL view, the corporation can create sales data tables for the sales in each month, or for the sales in each quarter, etc. which will result in smaller tables. Yet, the corporation can process the data stored in these various tables as if they were stored in one large table by employing a UNION ALL view.
While the UNION ALL view provides significant advantages, it is however subject to some limitations. Specifically, performing INSERT operations against a UNION ALL view can raise issues as it may not be clear which base table of the UNION ALL view the INSERT operation should operate on as the constraints of the base tables and/or any before triggers applicable to them are not known by the UNION ALL view.
Published U.S. Patent application No. 2003/0204540 to Rielau et al. teaches one method of performing an INSERT against a UNION ALL view. While the method taught in Rielau et al. provides advantages over other known methods for performing INSERTs against UNION ALL views, it can be computationally expensive to perform.